﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;

namespace SiloManaging
{
    class Database
    {
        OleDbConnection _Connection;
        OleDbCommand _Command;
        OleDbDataAdapter _Adapter;
        string _DataSource;
        string _Password;
        public Database()
        {
            _Adapter = new OleDbDataAdapter();
            _DataSource = System.Windows.Forms.Application.StartupPath + @"\Database\SiloInventory.mdb";
        }
        
        public string DataSource
        {
            get { return _DataSource; }
            set { _DataSource = value; }
        }
        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }        
        public void OpenConnection()
        {
            if (_DataSource == null)
                throw new Exception("Database file isn't defined");
            else if (!File.Exists(_DataSource))
                throw new Exception("Database file isn't exists");
            else
            {
                string constr = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + _DataSource + ";";
                if (_Password != null)
                    constr += "Jet OLEDB:Database Password=" + _Password;
                _Connection = new OleDbConnection(constr);
                _Connection.Open();

            }

        }
        public void CloseConnection()
        {
            
                if (_Connection != null)
                 _Connection.Dispose(); 
           
         
        }
        public void ExecuteNonQuery(string _sql, List<OleDbParameter> parameters)
        {            
            try
            {
                OpenConnection();
                _Command = new OleDbCommand();
                _Command.CommandText = (_sql);
                _Command.CommandType = CommandType.Text;
                _Command.Connection = _Connection;
                if (parameters != null){
                    foreach (OleDbParameter i in parameters)
                        _Command.Parameters.Add(i);
                    }
                _Command.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception ex)
            {
                throw ex;
                //System.Windows.Forms.MessageBox.Show(ex.Message);
            }            
        }
        public DataTable Query(string sql, List<OleDbParameter> parameters)
        {                
                try
                {
                    OpenConnection();
                    DataSet _data = new DataSet();
                    _Command = new OleDbCommand(sql, _Connection);
                    if (parameters != null)
                    {
                        foreach (OleDbParameter i in parameters)
                            _Command.Parameters.Add(i);
                    }
                    _Adapter = new OleDbDataAdapter(_Command);
                    _Adapter.Fill(_data, "MyTable");
                    CloseConnection();
                    return _data.Tables[0];
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                
            
        }
    }
}
